package cn.ygh.bbo.generator.service;


import cn.ygh.bbo.common.utils.StringUtils;
import cn.ygh.bbo.generator.config.Config;
import cn.ygh.bbo.generator.entity.Clazz;
import cn.ygh.bbo.generator.entity.Field;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author ygh
 * @Type DbUtils.java
 * @Desc
 * @date 2019/10/9
 * @Version V2.0
 */
public class DbService {

    private static final String SQL = "SELECT * FROM ";
    private static final String TABLE_SQL="select TABLE_NAME,TABLE_COMMENT from INFORMATION_SCHEMA.Tables where table_schema = (select database())";
    private Connection connection=null;

    /**
     * 获取所有的对象属性
     * @param
     * @return
     */
    public List<Clazz>getClazzs() throws SQLException {
        List<Clazz>tables=new ArrayList<Clazz>();
        Connection conn = getConn();
        DatabaseMetaData dbmd=conn.getMetaData();
        Statement pStemt = null;
        pStemt=conn.createStatement();
        ResultSet resultSet = pStemt.executeQuery(TABLE_SQL);
        while (resultSet.next()){
            Clazz table=new Clazz();
            String clazzName=resultSet.getString("TABLE_NAME");
            table.setClazzName(clazzName);
            table.setName(StringUtils.underline2Camel(clazzName, false));
            table.setRemark(resultSet.getString("TABLE_COMMENT"));
            ResultSet fields = dbmd.getColumns(null, "%", resultSet.getString("TABLE_NAME"), "%");
            table.setFields(getFields(resultSet.getString("TABLE_NAME"),fields));
            table.setId(table.getFields().get(0));
            tables.add(table);
        }
        closeConn();
        return tables;
    }

    /**
     * 获取所有的字段属性
     * @param tableName
     * @param resultSet
     * @return
     * @throws SQLException
     */
    private List<Field>getFields(String tableName, ResultSet resultSet) throws SQLException {
        Config config=Config.getInstance();
        List<Field>fields=new ArrayList<Field>();
        while (resultSet.next()){
            Field field=new Field();
            String fieldName=resultSet.getString("COLUMN_NAME");
            field.setFieldName(fieldName);
            field.setName(StringUtils.underline2Camel(fieldName, true));
            field.setNullable(resultSet.getBoolean("NULLABLE"));
            field.setType(config.getTypes().get(resultSet.getString("TYPE_NAME")));
            if (resultSet.getBoolean("IS_AUTOINCREMENT")){
                field.setAuto(true);
            }
            field.setRemark(getColumnComments(resultSet.getString("COLUMN_NAME"),tableName));
            if (fields.size()==0){
                field.setPrimary(true);
            }
            fields.add(field);
        }
        return fields;
    }

    /**
     * 获取表中字段的所有注释
     * @param tableName
     * @return
     */
    private String getColumnComments(String fieldName,String tableName) throws SQLException {
        //与数据库的连接
        Connection conn = getConn();
        Statement pStemt = null;
        ResultSet rs = null;
        try {
            pStemt = conn.createStatement();
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                if (rs.getString("Field").equals(fieldName)){
                    return rs.getString("Comment");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignored) {
                }
            }
        }
        return "";
    }

    /**
     * 获取连接
     * @return
     * @throws SQLException
     */
    private Connection getConn() throws SQLException {
        if (connection!=null){
            return connection;
        }
        synchronized (DbService.class){
            if (connection!=null){
                return connection;
            }
            Config config=Config.getInstance();
            connection=DriverManager.getConnection(config.getUrl(),config.getUsername(),config.getPassword());
        }
        return connection;
    }

    private void closeConn(){
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            connection=null;
        }
    }
}
